package com.ratingbay.console.dao;

import java.io.Serializable;
import java.sql.Timestamp;
import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

import com.ratingbay.console.model.Game;
import com.ratingbay.console.model.Tweet;
import com.ratingbay.console.model.TwitterUser;

public interface TweetDAO extends JpaRepository<Tweet, Long> {
	
	@Query(value="select min(t.id) as id from (select * from tweet where id_game = ?1 and is_retweet = false "
			+ "and create_date BETWEEN ?2 and ?3 order by retweet_count desc limit 600)t "
			+ "group by t.content order by max(t.retweet_count) desc,max(t.id) limit ?4 offset ?5*?4",
			nativeQuery=true)
	public List<Tweet> findByLIdGameAndCreateDateBetweenOrderByRetweetCount(Long idGame, Timestamp starttimestamp, Timestamp endtimestamp,int pageSize,int pageNum);
	
	@Query(value="select min(t.id) as id from (select * from tweet where id_game = ?1 and sentiment_type = 2 "
			+ "and create_date BETWEEN ?2 and ?3 order by sentiment_probability desc limit 600)t "
			+ "group by t.content order by max(t.sentiment_probability) desc,max(t.id) limit ?4 offset ?5*?4",
			nativeQuery=true)
	public List<Tweet> findByIdGameAndCreateDateBetweenOrderBySentimentProbabilityAsc(Long idGame, Timestamp starttimestamp, Timestamp endtimestamp,int pageSize,int pageNum);
	
	@Query(value="select min(t.id) as id from (select * from tweet where id_game = ?1 and sentiment_type = 1 "
			+ "and create_date BETWEEN ?2 and ?3 order by sentiment_probability desc limit 600)t "
			+ "group by t.content order by max(t.sentiment_probability) desc,max(t.id) limit ?4 offset ?5*?4",
			nativeQuery=true)
	public List<Tweet> findByIdGameAndCreateDateBetweenOrderBySentimentProbabilityDesc(Long idGame, Timestamp starttimestamp, Timestamp endtimestamp,int pageSize,int pageNum);
	
	@Query(value="select min(t.id) as id from (select * from tweet where id_game = ?1 "
			+ "and create_date BETWEEN ?2 and ?3 order by create_date desc limit 600)t "
			+ "group by t.content order by max(t.create_date) desc,max(t.id) limit ?4 offset ?5*?4",
			nativeQuery=true)
	public List<Tweet> findByIdGameAndCreateDateBetweenOrderByCreateDate(Long idGame, Timestamp starttimestamp, Timestamp endtimestamp,int pageSize,int pageNum);
	
	public Page<Tweet> findAll(Pageable pageable);

	@Query("select t from Tweet t where t.createDate between ?1 and ?2")
	public Page<Tweet> findByCreateDateBetween(Timestamp startdate, Timestamp finishdate, Pageable pageable);

	@Query("select t from Tweet t where t.userId = ?3 and t.createDate between ?1 and ?2")
	public Page<Tweet> findByUserIdAndCreateDateBetween(Timestamp startdate, Timestamp finishdate, Long userid, Pageable pagable);

	@Query("select t from Tweet t where t.idGame = ?1 and t.createDate between ?2 and ?3")
	public Page<Tweet> findByIdGameAndCreateDateBetween(Long idGame, Timestamp starttimestamp, Timestamp endtimestamp, Pageable pagable);

	@Query("select t from Tweet t where t.idGame = ?1 and t.createDate between ?3 and ?4 and t.userId in ?2")
	public Page<Tweet> findByIdGameAndCreateDateBetweenAndUsers(Long idGame, List<Long> userids, Timestamp starttimestamp, Timestamp endtimestamp, Pageable pagable);

	@Query(
			value = "select t.* from tweet t where t.id_game = ?1 and t.user_id = ?2 order by create_date desc limit ?3",
			nativeQuery = true
			)
	public List<Tweet> findByIdGameAndUser(Long idGame, Long userids, int pageSize);
	
	@Query("select t from Tweet t where t.userId in ?1 and t.createDate between ?2 and ?3 and t.idGame = ?4")
	public Page<Tweet> findByUserIdInAndCreateDateBetweenAndIdGame(List<Long> twitterUserIds, Timestamp starttimestamp, Timestamp endtimestamp, Long idGame, Pageable pagable);

	@Query("select t from Tweet t where t.userId in ?3 and t.createDate between ?1 and ?2")
	public Page<Tweet> findByUserIdInAndCreateDateBetween(Timestamp starttimestamp, Timestamp endtimestamp, List<Long> twitterUserIds, Pageable pagable);

	@Query("select t from Tweet t where t.id in ?1")
	public Page<Tweet> findIdIn(List<Long> ids, Pageable pageable);
	
	public List<Tweet> findByUserId(Long userid);

	@Query("select t from Tweet t where t.userId = ?1")
	public List<Tweet> findByUserIdPage(Long userid, Pageable pageable);

	@Query("select t from Tweet t where t.idGame = ?1")
	public Page<Tweet> findByIdGame(Long idGame, Pageable pagable);

	@Query("select t from Tweet t where t.idGame = ?1 and t.sentimentType = 1")
	public Page<Tweet> findPostiveTweetByIdGame(Long idGame, Pageable pagable);
	
	@Query("select t from Tweet t where t.idGame = ?1 and t.sentimentType = 2")
	public Page<Tweet> findNegitiveTweetByIdGame(Long idGame, Pageable pagable);

	//@Query("select count(t) from Tweet t where t.idGame = ?1")
	//public Long findCountByGame(Long idGame);

	@Query("select count(distinct t.idGame) from Tweet t where t.userId = ?1")
	public Long findGameCountByUser(Long tUserId);

    @Query(
    	value = "select * from tweet t where t.user_id = ?1 order by t.create_date desc limit 1", 
    	nativeQuery = true
    	)
	public Tweet findLatestTweetByUserId(Long tUserId);
    
    @Query(
    		value = "select  t.user_id from twitter_user t where t.username=?1 limit 1",
    		nativeQuery = true
    		)
    	public Long findByUsername(String uName);
    
    @Query(
		value = "select count(distinct t.user_id) from twitter_user t where LOWER(t.username) like LOWER(?1)", 
		nativeQuery = true
		)
	public Long findCountUserIdByUserNameLike(String uName);
	@Query(
		value = "select distinct t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLike(String uName, int pageNum, int pageSize);

	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.username ASC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeASCOrderUN(String uName, int pageNum, int pageSize);
	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.username DESC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeDESCOrderUN(String uName, int pageNum, int pageSize);

	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) GROUP BY t.user_id ORDER BY count(t.id_game) ASC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeASCOrderGC(String uName, int pageNum, int pageSize);
	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) GROUP BY t.user_id ORDER BY count(t.id_game) DESC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeDESCOrderGC(String uName, int pageNum, int pageSize);

	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.user_tweets_count ASC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeASCOrderTC(String uName, int pageNum, int pageSize);
	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.user_tweets_count DESC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeDESCOrderTC(String uName, int pageNum, int pageSize);

	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.user_friends_count ASC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeASCOrderFINGC(String uName, int pageNum, int pageSize);
	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.user_friends_count DESC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeDESCOrderFINGC(String uName, int pageNum, int pageSize);

	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.user_followers_count ASC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeASCOrderFEDC(String uName, int pageNum, int pageSize);
	@Query(
		value = "select t.user_id from twitter_user t where LOWER(t.username) like LOWER(?1) ORDER BY t.user_followers_count DESC limit ?3 offset ?2*?3",
		nativeQuery = true
		)
	public List<Long> findUserIdByUserNameLikeDESCOrderFEDC(String uName, int pageNum, int pageSize);

	@Query(value = "SELECT t FROM Tweet t, TweetLink tl WHERE t.id = tl.idTweet AND tl.idLink = ?1 AND t.userId in ?2")
	public Page<Tweet> findTweetsByLinkAndUsers(Long linkId, List<Long> tUserIds, Pageable pageable);
	
	@Query(
    	value = "SELECT t.* FROM tweet t, tweet_link tl WHERE t.id = tl.id_tweet AND tl.id_link = ?1 LIMIT ?3 OFFSET ?2*?3", 
    	nativeQuery = true
    	)
	public List<Tweet> findTweetsByLink(Long linkId , int pageNum, int pageSize);
	
	
	@Query(
	    	value = "SELECT * FROM tweet WHERE is_retweet = false and retweet_count > 20 and create_date BETWEEN ?1 and ?2 ORDER BY retweet_count desc LIMIT 5", 
	    	nativeQuery = true
	    	)
	public List<Tweet> findPopularTweets(Timestamp startdate, Timestamp finishdate);
	
	@Query(
	    	value = "SELECT * FROM tweet where is_retweet = false and create_date between ?1 and ?2 ORDER BY create_date desc LIMIT 5", 
	    	nativeQuery = true
	    	)
	public List<Tweet> findRecentTweets(Timestamp begin,Timestamp end);
	//home page get most popular tweet 
	
	@Query(value = "SELECT t FROM Tweet t WHERE t.id = ?1")
	public List<Tweet> findById(Long id);
	
	
}

